package com.ctg.behavior.calc.impala.transfer.userlist;

import java.util.ArrayList;
import java.util.List;

import org.springframework.stereotype.Component;

import com.alibaba.fastjson.JSON;
import com.ctg.behavior.calc.common.CtCalcDate;
import com.ctg.behavior.calc.common.CtSQLParam;
import com.ctg.behavior.calc.impala.report.retentions.CtRetentionsReport;
import com.ctg.behavior.calc.impala.transfer.CtFilterTransfer;
import com.ctg.behavior.calc.utils.CtCalcPropertyUtils;
import com.ctg.behavior.calc.utils.CtCalcSQLUtils;
import com.ctg.behavior.common.calc.CtCalc;
import com.ctg.behavior.common.calc.CtCalcEvent;
import com.ctg.behavior.common.calc.CtCalcFilter;
import com.ctg.behavior.common.calc.CtCalcPropertyType;
import com.ctg.behavior.common.consts.CtFieldConsts;
import com.ctg.behavior.common.consts.CtPropertyConsts;
import com.ctg.behavior.utils.CtDateUtils;

import lombok.extern.slf4j.Slf4j;

@Slf4j
@Component
public class CtRetentionsUserListTransfer extends CtFilterTransfer {
	
	@Override
	public String doTransfer(CtCalc calcObject, CtSQLParam sqlParam) {

		if (!(calcObject instanceof CtRetentionsReport)) {
			log.warn("calcObject is not CtRetentionsReport, class={}", calcObject.getClass());
			log.warn("calcObject: {}", JSON.toJSON(calcObject));
			return "";
		}
		CtRetentionsReport report = (CtRetentionsReport) calcObject;
		log.info("measure={}", JSON.toJSONString(report));

		List<CtCalcPropertyType> ptypes = new ArrayList<>();
		{
			List<String> byFields = report.getByFields();
			for (String oneField : byFields) {
				CtCalcPropertyType ptype = CtCalcPropertyUtils.getValueType(oneField);
				ptypes.add(ptype);
			}
		}

		StringBuilder impalaSQL = new StringBuilder();

		String cell0DaySQL = this.transferRowCell0Day(report, ptypes);
		log.info("cell0DaySQL={}", cell0DaySQL);
		impalaSQL.append(cell0DaySQL);

		for (CtCalcDate cellDate : report.getCellDates()) {
			String oneCellSQL = this.transferRowCellsOtherDay(report, cellDate, ptypes);
			log.info("oneCellSQL={}", oneCellSQL);
			impalaSQL.append(" UNION ").append(oneCellSQL);
		}

		return impalaSQL.toString();

	}

	public String transferRowCell0Day(CtRetentionsReport report, List<CtCalcPropertyType> ptypes) {

		StringBuilder impalaSQL = new StringBuilder();
		// tp001. 顶层 SELECT部分，统计人数(user_id)
		{
			// impalaSQL.append(" SELECT T0.event AS event ");
			impalaSQL.append(" SELECT \"").append(report.getWhichDay()).append("\" AS ").append(CtFieldConsts.FIELD_KEY_CALC_DATE);
			impalaSQL.append(" , T0.event AS event ");
			for (CtCalcPropertyType ptype : ptypes) {
				impalaSQL.append(" , T0.").append(ptype.getProperty());
				impalaSQL.append(" AS ").append(ptype.getProperty());
			}
			// 按日统计：则为本日的日期；若按周统计：则为本周中查询出最小日期；按月统计：则为本月中查询出最小日期
			// impalaSQL.append(" , min(T0.eventDate) AS eventDate ");
			impalaSQL.append(" , \"").append(report.getTheFirstEventFromDate()).append("\" AS eventDate ");
			impalaSQL.append(" , min(T0.eventTime) AS eventTime ");

			impalaSQL.append(" , count(*) AS total ");
			impalaSQL.append(" FROM ( ");
		}

		String transferedEventSQL = this.transferFirstEvent(report, ptypes);
		log.info("transferedEventSQL={}", transferedEventSQL);
		impalaSQL.append(transferedEventSQL);

		// tp002. 顶层 group by
		{
			impalaSQL.append(" ) T0 ");
			impalaSQL.append(" GROUP BY ");
			impalaSQL.append(" T0.event ");
			for (CtCalcPropertyType ptype : ptypes) {
				if (CtPropertyConsts.PROPERTY_EVENT.equals(ptype.getType())) {
					impalaSQL.append(" , T0.").append(ptype.getProperty());
				} else if (CtPropertyConsts.PROPERTY_USER.equals(ptype.getType())) {
					impalaSQL.append(" , T0.").append(ptype.getProperty());
				}
			}
		}

		return impalaSQL.toString();
	}

	public String transferRowCellsOtherDay(CtRetentionsReport report, CtCalcDate cellDate, List<CtCalcPropertyType> ptypes) {

		StringBuilder impalaSQL = new StringBuilder();
		// tp001. 顶层 SELECT部分，统计人数(user_id)
		{
			// impalaSQL.append(" SELECT T0.event AS event ");
			impalaSQL.append(" SELECT \"").append(report.getWhichDay()).append("\" AS ").append(CtFieldConsts.FIELD_KEY_CALC_DATE);
			impalaSQL.append(" , T0.event AS event ");
			for (CtCalcPropertyType ptype : ptypes) {
				impalaSQL.append(" , T0.").append(ptype.getProperty());
				impalaSQL.append(" AS ").append(ptype.getProperty());
			}
			// 按日统计：则为本日的日期；若按周统计：则为本周中查询出最小日期；按月统计：则为本月中查询出最小日期
			// impalaSQL.append(" , min(T0.eventDate) AS eventDate ");
			String tEventDate = CtDateUtils.dateToString(cellDate.getFromDate(), CtDateUtils.YYYY_MM_DD);
			impalaSQL.append(" , \"").append(tEventDate).append("\" AS eventDate ");
			impalaSQL.append(" , min(T0.eventTime) AS eventTime ");

			impalaSQL.append(" , count(*) AS total ");
			impalaSQL.append(" FROM ( ");
		}

		String transferedEventSQL = this.transferFirstAndSecondEvent(report, cellDate, ptypes);
		log.info("transferedEventSQL={}", transferedEventSQL);
		impalaSQL.append(transferedEventSQL);

		// tp002. 顶层 group by
		{
			impalaSQL.append(" ) T0 ");
			impalaSQL.append(" GROUP BY ");
			impalaSQL.append(" T0.event ");
			for (CtCalcPropertyType ptype : ptypes) {
				if (CtPropertyConsts.PROPERTY_EVENT.equals(ptype.getType())) {
					impalaSQL.append(" , T0.").append(ptype.getProperty());
				} else if (CtPropertyConsts.PROPERTY_USER.equals(ptype.getType())) {
					impalaSQL.append(" , T0.").append(ptype.getProperty());
				}
			}
		}

		return impalaSQL.toString();
	}

	public String transferFirstAndSecondEvent(CtRetentionsReport report, CtCalcDate cellDate, List<CtCalcPropertyType> ptypes) {

		StringBuilder impalaSQL = new StringBuilder();

		// 1. SELECT 部分：
		{
			impalaSQL.append(" SELECT te02.user_id AS uesrId, te02.event AS event ");
			for (CtCalcPropertyType ptype : ptypes) {
				if (CtPropertyConsts.PROPERTY_EVENT.equals(ptype.getType())) {
					impalaSQL.append(" , te02.").append(ptype.getProperty()).append(" AS ").append(ptype.getProperty());
				} else if (CtPropertyConsts.PROPERTY_USER.equals(ptype.getType())) {
					impalaSQL.append(" , tu02.").append(ptype.getProperty()).append(" AS ").append(ptype.getProperty());
				}
			}
			// 按日统计：则为本日的日期；若按周统计：则为本周中查询出最小日期；按月统计：则为本月中查询出最小日期
			impalaSQL.append(" , min(te02.event_date) AS eventDate ");
			impalaSQL.append(" , min(te02.event_time) AS eventTime ");
		}
		// 2. 表关联
		{
			impalaSQL.append(" FROM a_event_parquet_").append(report.getAppId()).append(" te02 ");
			if (!report.getFilter().getConditions().isEmpty()) {
				// 2.1 如果有用户属性筛选条件，则进行用户表关联( a_user_kudu_{appId})
				impalaSQL.append(" JOIN  a_user_kudu_").append(report.getAppId()).append(" tu02 ");
				impalaSQL.append(" ON te02.user_id = tu02.user_id ");
			}
			// 2.2 Second Event的用户必在First Event的用户中，且时间要晚于First Event
			String firstEventSQL = this.transferFirstEvent(report, ptypes);
			impalaSQL.append(" JOIN ( ").append(firstEventSQL).append(" ) tuj ");
			impalaSQL.append(" ON te02.user_id = tuj.userId ");

			impalaSQL.append(" WHERE 1=1 ");
		}
		// 3. 留存行为 - [后续行为]及其的筛选条件(filter)
		{
			impalaSQL.append(" AND ( ");
			// [后续行为](即：第二件事件)的筛选条件
			CtCalcEvent secondEvent = report.getSecondEvent();
			// 流失 与 留存 此处不区分
			impalaSQL.append(" te02.event = '").append(secondEvent.getEventId()).append("' ");
			// 时间限制 - First Event对Second Event的时间限制
			impalaSQL.append(" AND te02.event_time > tuj.eventTime ");
			CtCalcFilter filter = secondEvent.getFilter();
			String conditionsSQL = super.doTransfer(filter, CtSQLParam.createAliasParam("te02"));
			if (!CtCalcSQLUtils.isEmptyCondtions(conditionsSQL)) {
				impalaSQL.append(" AND ").append(conditionsSQL);
			}
			// 本Cell的时间限制 - fromDate/toDate
			String toEventDateText = CtDateUtils.dateToString(cellDate.getToDate(), CtDateUtils.YYYY_MM_DD);
			String fromEventDate = CtDateUtils.dateToString(cellDate.getFromDate(), CtDateUtils.YYYY_MM_DD);
			impalaSQL.append(" AND te02.event_date <= '").append(toEventDateText).append("' ");
			impalaSQL.append(" AND te02.event_date >= '").append(fromEventDate).append("' ");
			impalaSQL.append(" ) ");
		}

		// 4. 查询条件 - 且用户符合
		{
			CtCalcFilter filter = report.getFilter();
			String conditionsSQL = super.doTransfer(filter, CtSQLParam.createAliasParam("tu02"));
			if (!CtCalcSQLUtils.isEmptyCondtions(conditionsSQL)) {
				impalaSQL.append(" AND ").append(conditionsSQL);
			}
		}
		// 5. 分组(group by) - 区分
		{
			impalaSQL.append(" GROUP BY ");
			impalaSQL.append(" te02.user_id , te02.event ");
			for (CtCalcPropertyType ptype : ptypes) {
				if (CtPropertyConsts.PROPERTY_EVENT.equals(ptype.getType())) {
					impalaSQL.append(" , ").append(" te02.").append(ptype.getProperty());
				} else if (CtPropertyConsts.PROPERTY_USER.equals(ptype.getType())) {
					impalaSQL.append(" , ").append(" tu02.").append(ptype.getProperty());
				}
			}
		}

		return impalaSQL.toString();
	}

	public String transferFirstEvent(CtRetentionsReport report, List<CtCalcPropertyType> ptypes) {

		StringBuilder impalaSQL = new StringBuilder();

		// 1. SELECT 部分：
		{
			impalaSQL.append(" SELECT te01.user_id AS userId, te01.event AS event ");
			for (CtCalcPropertyType ptype : ptypes) {
				if (CtPropertyConsts.PROPERTY_EVENT.equals(ptype.getType())) {
					impalaSQL.append(" , te01.").append(ptype.getProperty()).append(" AS ").append(ptype.getProperty());
				} else if (CtPropertyConsts.PROPERTY_USER.equals(ptype.getType())) {
					impalaSQL.append(" , tu01.").append(ptype.getProperty()).append(" AS ").append(ptype.getProperty());
				}
			}
			impalaSQL.append(", min(te01.event_date) AS eventDate ");
			impalaSQL.append(", min(te01.event_time) AS eventTime ");
		}
		// 2. 表关联
		{
			impalaSQL.append(" FROM a_event_parquet_").append(report.getAppId()).append(" te01 ");
			if (!report.getFilter().getConditions().isEmpty()) {
				// 如果有用户属性筛选条件，则进行用户表关联( a_user_kudu_{appId})
				impalaSQL.append(" LEFT JOIN  a_user_kudu_").append(report.getAppId()).append(" tu01 ");
				impalaSQL.append(" ON te01.user_id = tu01.user_id ");
			}
			impalaSQL.append(" WHERE 1=1 ");
		}
		// 3. 留存行为 - [初始行为]及其的筛选条件(filter)
		{
			impalaSQL.append(" AND ( ");
			CtCalcEvent firstEvent = report.getFirstEvent();
			impalaSQL.append(" te01.event = '").append(firstEvent.getEventId()).append("' ");
			CtCalcFilter filter = firstEvent.getFilter();
			String conditionsSQL = super.doTransfer(filter, CtSQLParam.createAliasParam("te01"));
			if (!CtCalcSQLUtils.isEmptyCondtions(conditionsSQL)) {
				impalaSQL.append(" AND ").append(conditionsSQL);
			}
			impalaSQL.append(" ) ");
		}
		// 4. 查询条件 - 且用户符合
		{
			CtCalcFilter filter = report.getFilter();
			String conditionsSQL = super.doTransfer(filter, CtSQLParam.createAliasParam("tu"));
			if (!CtCalcSQLUtils.isEmptyCondtions(conditionsSQL)) {
				impalaSQL.append(" AND ").append(conditionsSQL);
			}
		}
		// 5. 时间限制 - fromDate/toDate
		{
			impalaSQL.append(" AND te01.event_date <= '").append(report.getTheFirstEventToDate()).append("' ");
			impalaSQL.append(" AND te01.event_date >= '").append(report.getTheFirstEventFromDate()).append("' ");
		}
		// 6. 分组(group by) - 区分
		{
			impalaSQL.append(" GROUP BY ");
			impalaSQL.append(" te01.user_id , te01.event ");
			for (CtCalcPropertyType ptype : ptypes) {
				if (CtPropertyConsts.PROPERTY_EVENT.equals(ptype.getType())) {
					impalaSQL.append(", te01.").append(ptype.getProperty());
				} else if (CtPropertyConsts.PROPERTY_USER.equals(ptype.getType())) {
					impalaSQL.append(", tu01.").append(ptype.getProperty());
				}
			}
		}

		return impalaSQL.toString();
	}


	
}
